# -*- coding: utf-8 -*-
"""
数据库连接和操作工具类
"""

import pymysql
import redis
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
from loguru import logger
from typing import List, Dict, Any, Optional
import json
from datetime import datetime

from crawler.config.settings import DATABASE_CONFIG, REDIS_CONFIG


class DatabaseManager:
    """数据库管理器"""
    
    def __init__(self):
        self.mysql_engine = None
        self.session_factory = None
        self.redis_client = None
        self._init_mysql()
        self._init_redis()
    
    def _init_mysql(self):
        """初始化MySQL连接"""
        try:
            db_url = f"mysql+pymysql://{DATABASE_CONFIG['user']}:{DATABASE_CONFIG['password']}@{DATABASE_CONFIG['host']}:{DATABASE_CONFIG['port']}/{DATABASE_CONFIG['database']}?charset={DATABASE_CONFIG['charset']}"
            self.mysql_engine = create_engine(
                db_url,
                pool_size=20,
                max_overflow=30,
                pool_recycle=3600,
                pool_pre_ping=True,
                echo=False
            )
            self.session_factory = sessionmaker(bind=self.mysql_engine)
            logger.info("MySQL连接初始化成功")
        except Exception as e:
            logger.error(f"MySQL连接初始化失败: {e}")
            raise
    
    def _init_redis(self):
        """初始化Redis连接"""
        try:
            self.redis_client = redis.Redis(
                host=REDIS_CONFIG['host'],
                port=REDIS_CONFIG['port'],
                password=REDIS_CONFIG['password'],
                db=REDIS_CONFIG['db'],
                decode_responses=True
            )
            # 测试连接
            self.redis_client.ping()
            logger.info("Redis连接初始化成功")
        except Exception as e:
            logger.error(f"Redis连接初始化失败: {e}")
            raise
    
    def execute_sql(self, sql: str, params: Dict = None) -> List[Dict]:
        """执行SQL查询"""
        session = self.session_factory()
        try:
            result = session.execute(text(sql), params or {})
            if result.returns_rows:
                columns = result.keys()
                rows = result.fetchall()
                data = [dict(zip(columns, row)) for row in rows]
                session.commit()
                return data
            else:
                session.commit()
                return []
        except Exception as e:
            session.rollback()
            logger.error(f"SQL执行失败: {sql}, 错误: {e}")
            raise
        finally:
            session.close()
    
    def execute_query(self, sql: str, params: tuple = None) -> List[Dict]:
        """执行SQL查询（兼容性方法）"""
        try:
            if params:
                # 将tuple参数转换为dict格式
                param_dict = {}
                # 处理%s占位符
                if '%s' in sql:
                    # 替换%s占位符为命名占位符
                    new_sql = sql
                    for i, param in enumerate(params):
                        param_key = f"param_{i}"
                        param_dict[param_key] = param
                        new_sql = new_sql.replace('%s', f":{param_key}", 1)
                    return self.execute_sql(new_sql, param_dict)
                # 处理?占位符
                elif '?' in sql:
                    sql_parts = sql.split('?')
                    new_sql = sql_parts[0]
                    for i, param in enumerate(params):
                        param_key = f"param_{i}"
                        param_dict[param_key] = param
                        if i < len(sql_parts) - 1:
                            new_sql += f":{param_key}" + sql_parts[i + 1]
                    return self.execute_sql(new_sql, param_dict)
                else:
                    return self.execute_sql(sql, param_dict)
            else:
                return self.execute_sql(sql)
        except Exception as e:
            logger.error(f"SQL查询执行失败: {e}")
            return []
    
    def insert_lottery_result(self, lottery_type: str, period: str, numbers: List[str], 
                            draw_time: datetime, extra_data: Dict = None) -> bool:
        """插入开奖结果"""
        try:
            sql = """
            INSERT INTO lottery_results 
            (lottery_type, period, numbers, draw_time, extra_data, created_at) 
            VALUES (:lottery_type, :period, :numbers, :draw_time, :extra_data, NOW())
            ON DUPLICATE KEY UPDATE 
            numbers = VALUES(numbers),
            extra_data = VALUES(extra_data),
            updated_at = NOW()
            """
            
            params = {
                'lottery_type': lottery_type,
                'period': period,
                'numbers': json.dumps(numbers),
                'draw_time': draw_time,
                'extra_data': json.dumps(extra_data or {})
            }
            
            self.execute_sql(sql, params)
            logger.info(f"插入开奖结果成功: {lottery_type} {period}")
            return True
        except Exception as e:
            logger.error(f"插入开奖结果失败: {e}")
            return False
    
    def get_latest_period(self, lottery_type: str) -> Optional[str]:
        """获取最新期号"""
        try:
            sql = """
            SELECT period FROM lottery_results 
            WHERE lottery_type = :lottery_type 
            ORDER BY period DESC LIMIT 1
            """
            result = self.execute_sql(sql, {'lottery_type': lottery_type})
            return result[0]['period'] if result else None
        except Exception as e:
            logger.error(f"获取最新期号失败: {e}")
            return None
    
    def cache_set(self, key: str, value: Any, expire: int = 3600) -> bool:
        """设置缓存"""
        try:
            if isinstance(value, (dict, list)):
                value = json.dumps(value, ensure_ascii=False)
            self.redis_client.setex(key, expire, value)
            return True
        except Exception as e:
            logger.error(f"设置缓存失败: {key}, 错误: {e}")
            return False
    
    def cache_get(self, key: str) -> Any:
        """获取缓存"""
        try:
            value = self.redis_client.get(key)
            if value:
                try:
                    return json.loads(value)
                except json.JSONDecodeError:
                    return value
            return None
        except Exception as e:
            logger.error(f"获取缓存失败: {key}, 错误: {e}")
            return None
    
    def cache_delete(self, key: str) -> bool:
        """删除缓存"""
        try:
            self.redis_client.delete(key)
            return True
        except Exception as e:
            logger.error(f"删除缓存失败: {key}, 错误: {e}")
            return False
    
    def publish_message(self, channel: str, message: Dict) -> bool:
        """发布消息到Redis频道"""
        try:
            self.redis_client.publish(channel, json.dumps(message, ensure_ascii=False))
            return True
        except Exception as e:
            logger.error(f"发布消息失败: {channel}, 错误: {e}")
            return False
    
    def test_connection(self) -> bool:
        """测试数据库连接"""
        try:
            # 测试MySQL连接
            if self.session_factory:
                session = self.session_factory()
                try:
                    session.execute(text("SELECT 1"))
                    session.commit()
                finally:
                    session.close()
            # 测试Redis连接
            if self.redis_client:
                self.redis_client.ping()
            return True
        except Exception as e:
            logger.error(f"数据库连接测试失败: {e}")
            return False

    def close(self):
        """关闭连接"""
        try:
            if self.mysql_engine:
                self.mysql_engine.dispose()
            if self.redis_client:
                self.redis_client.close()
            logger.info("数据库连接已关闭")
        except Exception as e:
            logger.error(f"关闭数据库连接失败: {e}")


# 全局数据库管理器实例
db_manager = DatabaseManager()